/*
Copyright 2024 MySQL Parser Project

Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at

    http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
*/

package main

import (
	"fmt"
	"log"
	"time"

	"mysql-parser/sqlparser"
)

func main() {
	fmt.Println("=== SQL性能优化示例 ===")
	fmt.Println()

	// 查询优化前后对比
	fmt.Println("=== 查询优化前后对比 ===")
	
	optimizationExamples := []struct {
		name   string
		before string
		after  string
		tips   string
	}{
		{
			name: "避免SELECT *",
			before: "SELECT * FROM users WHERE age > 25",
			after:  "SELECT id, name, email FROM users WHERE age > 25",
			tips:   "只选择需要的列，减少数据传输量",
		},
		{
			name: "使用索引优化WHERE条件",
			before: "SELECT name FROM users WHERE YEAR(created_at) = 2023",
			after:  "SELECT name FROM users WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01'",
			tips:   "避免在WHERE条件中使用函数，使索引能够生效",
		},
		{
			name: "优化JOIN查询",
			before: "SELECT u.name, p.title FROM users u, posts p WHERE u.id = p.user_id",
			after:  "SELECT u.name, p.title FROM users u INNER JOIN posts p ON u.id = p.user_id",
			tips:   "使用显式JOIN语法，提高查询可读性和性能",
		},
		{
			name: "优化子查询为JOIN",
			before: "SELECT name FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 1000)",
			after:  "SELECT DISTINCT u.name FROM users u INNER JOIN orders o ON u.id = o.user_id WHERE o.total > 1000",
			tips:   "将子查询转换为JOIN，通常性能更好",
		},
		{
			name: "使用LIMIT优化大结果集",
			before: "SELECT * FROM products ORDER BY created_at DESC",
			after:  "SELECT id, name, price FROM products ORDER BY created_at DESC LIMIT 20",
			tips:   "对大表查询使用LIMIT限制结果集大小",
		},
		{
			name: "优化OR条件为UNION",
			before: "SELECT * FROM products WHERE category_id = 1 OR price < 100",
			after:  "SELECT * FROM products WHERE category_id = 1 UNION SELECT * FROM products WHERE price < 100 AND category_id != 1",
			tips:   "当OR条件无法使用索引时，考虑使用UNION",
		},
		{
			name: "优化COUNT查询",
			before: "SELECT COUNT(*) FROM orders WHERE user_id = 123",
			after:  "SELECT COUNT(1) FROM orders WHERE user_id = 123",
			tips:   "使用COUNT(1)代替COUNT(*)，在某些情况下性能更好",
		},
		{
			name: "使用EXISTS代替IN",
			before: "SELECT name FROM users WHERE id IN (SELECT user_id FROM orders)",
			after:  "SELECT name FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id)",
			tips:   "对于大数据集，EXISTS通常比IN性能更好",
		},
	}

	for _, example := range optimizationExamples {
		fmt.Printf("=== %s ===\n", example.name)
		fmt.Printf("优化前: %s\n", example.before)
		fmt.Printf("优化后: %s\n", example.after)
		fmt.Printf("优化建议: %s\n", example.tips)

		// 分析优化前的查询
		fmt.Println("\n优化前分析:")
		analyzeQuery(example.before)

		// 分析优化后的查询
		fmt.Println("优化后分析:")
		analyzeQuery(example.after)

		fmt.Println("---")
		fmt.Println()
	}

	// 索引优化建议
	fmt.Println("=== 索引优化建议 ===")
	indexOptimizationQueries := []string{
		"SELECT * FROM users WHERE email = 'john@example.com'", // 建议在email上创建索引
		"SELECT * FROM orders WHERE user_id = 123 AND status = 'pending'", // 建议创建复合索引(user_id, status)
		"SELECT * FROM products WHERE category_id = 1 ORDER BY price DESC", // 建议创建复合索引(category_id, price)
		"SELECT COUNT(*) FROM posts WHERE user_id = 456 AND created_at > '2023-01-01'", // 建议创建复合索引(user_id, created_at)
		"SELECT * FROM users WHERE name LIKE 'John%'", // 建议在name上创建索引
	}

	for i, sql := range indexOptimizationQueries {
		fmt.Printf("索引优化示例 %d: %s\n", i+1, sql)
		analyzeQuery(sql)
		provideIndexSuggestion(sql)
		fmt.Println()
	}

	// 查询复杂度分析
	fmt.Println("=== 查询复杂度分析 ===")
	complexityQueries := []string{
		"SELECT id FROM users", // Simple
		"SELECT u.name, COUNT(p.id) FROM users u LEFT JOIN posts p ON u.id = p.user_id GROUP BY u.id", // Moderate
		"SELECT u.name, COUNT(p.id), AVG(r.rating) FROM users u LEFT JOIN posts p ON u.id = p.user_id LEFT JOIN reviews r ON p.id = r.post_id WHERE u.created_at > '2023-01-01' GROUP BY u.id HAVING COUNT(p.id) > 5 ORDER BY AVG(r.rating) DESC", // Complex
	}

	for i, sql := range complexityQueries {
		fmt.Printf("复杂度分析示例 %d: %s\n", i+1, sql)
		analyzeQuery(sql)
		fmt.Println()
	}

	// 性能监控查询示例
	fmt.Println("=== 性能监控查询示例 ===")
	monitoringQueries := []string{
		// 慢查询监控
		`SELECT 
			query_text,
			execution_count,
			avg_execution_time,
			max_execution_time,
			total_execution_time
		FROM performance_schema.events_statements_summary_by_digest
		WHERE avg_execution_time > 1000000
		ORDER BY avg_execution_time DESC
		LIMIT 10`,

		// 表扫描监控
		`SELECT 
			table_schema,
			table_name,
			rows_examined,
			rows_sent,
			select_scan
		FROM performance_schema.table_io_waits_summary_by_table
		WHERE select_scan > 1000
		ORDER BY select_scan DESC`,

		// 索引使用情况
		`SELECT 
			object_schema,
			object_name,
			index_name,
			count_read,
			count_write,
			sum_timer_read,
			sum_timer_write
		FROM performance_schema.table_io_waits_summary_by_index_usage
		WHERE object_schema NOT IN ('mysql', 'performance_schema', 'information_schema')
		ORDER BY sum_timer_read DESC`,
	}

	for i, sql := range monitoringQueries {
		fmt.Printf("性能监控示例 %d:\n%s\n", i+1, sql)
		analyzeQuery(sql)
		fmt.Println("---")
		fmt.Println()
	}
}

// analyzeQuery 分析单个查询的性能特征
func analyzeQuery(sql string) {
	start := time.Now()
	
	stmt, err := sqlparser.Parse(sql)
	if err != nil {
		log.Printf("  解析错误: %v", err)
		return
	}

	parseTime := time.Since(start)

	// 提取表名
	tables := sqlparser.ExtractAllTables(stmt)
	fmt.Printf("  涉及的表: %v\n", tables)

	// 表分析
	tableAnalysis := sqlparser.AnalyzeTables(stmt)
	fmt.Printf("  表分析: 表数量=%d, 有连接=%v, 有子查询=%v\n",
		tableAnalysis.TableCount, tableAnalysis.HasJoins, tableAnalysis.HasSubquery)

	// 聚合分析
	aggAnalysis := sqlparser.AnalyzeAggregation(stmt)
	fmt.Printf("  聚合分析: 包含聚合=%v, 有GROUP BY=%v, 有HAVING=%v\n",
		aggAnalysis.HasAggregation, aggAnalysis.HasGroupBy, aggAnalysis.HasHaving)

	// 查询复杂度分析
	complexity := sqlparser.AnalyzeQueryComplexity(stmt)
	fmt.Printf("  复杂度: %s (分数: %d)\n",
		complexity.ComplexityLevel, complexity.ComplexityScore)

	fmt.Printf("  解析耗时: %v\n", parseTime)
}

// provideIndexSuggestion 根据查询提供索引建议
func provideIndexSuggestion(sql string) {
	stmt, err := sqlparser.Parse(sql)
	if err != nil {
		return
	}

	// 简单的索引建议逻辑
	tables := sqlparser.ExtractAllTables(stmt)
	complexity := sqlparser.AnalyzeQueryComplexity(stmt)

	fmt.Printf("  索引建议: ")
	if len(tables) == 1 {
		fmt.Printf("考虑在表 %s 的WHERE条件列上创建索引", tables[0])
	} else if complexity.TableAnalysis.HasJoins {
		fmt.Printf("考虑在JOIN条件的关联列上创建索引")
	}

	if complexity.AggregationAnalysis.HasGroupBy {
		fmt.Printf(", 在GROUP BY列上创建索引")
	}

	if complexity.ComplexityScore > 10 {
		fmt.Printf(", 考虑查询重构或分解")
	}
	fmt.Println()
}